/***********************************************************************************************************************************
 *    Name: SQL-Multi-Script.sql
 *  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Generate the database list for SQL Multi-Script.
 *
 * C:\Users\%USERNAME%\AppData\Roaming\Red Gate\SQL Multi Script
 */
USE DBAdmin;
GO
DECLARE @DList SYSNAME, @CServer SYSNAME, @Server SYSNAME, @PreviousDList SYSNAME, @xmltext NVARCHAR(MAX);
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT Path, ServerInstance, Label FROM dbo.ServerList WHERE Label NOT LIKE N'%(%)' ORDER BY Path, ServerInstance;
OPEN list;
SET NOCOUNT ON;

SET @xmltext= N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34--><multiScriptApplication version="2" type="multiScriptApplication"><databaseLists type="List_databaseList" version="1">';
WHILE (0=0) BEGIN;
  FETCH NEXT FROM list INTO @DList, @Server, @CServer;
  IF @@FETCH_STATUS <> 0 BREAK;
  IF ISNULL(@PreviousDList,N'') <> @DList BEGIN;
	IF @PreviousDList IS NOT Null SET @xmltext+= N'</databases><guid>'+CAST(NewID() AS NVARCHAR(36))+N'</guid></value>';					-- close previous group
	SET @xmltext+= N'<value version="2" type="databaseList"><name>'+@DList+N'</name><databases type="BindingList_database" version="1">';	-- start new group
  END;
  SET @xmltext+= N'<value version="5" type="database"><name>master</name><server>'+@Server+N'</server><integratedSecurity>True</integratedSecurity><connectionTimeout>12</connectionTimeout><protocol>2</protocol><packetSize>4096</packetSize><encrypted>False</encrypted><selected>True</selected><cserver>'+@CServer+N'</cserver></value>';
  SET @PreviousDList= @DList;
END;
CLOSE list;
DEALLOCATE list;
SET @xmltext+= N'</databases><guid>'+CAST(NewID() AS NVARCHAR(36))+N'</guid></value>';														-- close last group
SET @xmltext+= N'</databaseLists><addedServers type="List_server" version="1" /></multiScriptApplication>';									-- close the top level

SELECT
	[Application.dat]	= CAST(@xmltext AS XML),
	[Project.sms]		= CAST(N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34--><project version="2" type="project"><applicationOptions version="3" type="applicationOptions"><executionTimeout>0</executionTimeout><batchSeparator>GO</batchSeparator><displayFormat>0</displayFormat><maximumNonXMLDataRetrieved>65535</maximumNonXMLDataRetrieved><maximumXMLDataRetrieved>2097152</maximumXMLDataRetrieved><maximumCharactersPerColumn>256</maximumCharactersPerColumn><maximumParallelServers>13</maximumParallelServers><useParallelExecution>True</useParallelExecution></applicationOptions><scripts type="List_scriptRecord" version="1" /><errorMode>2</errorMode></project>' AS XML);
SET NOCOUNT OFF;
GO